﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Linq;
using MISAPP.Common;
using System.Data.Common;

namespace MISAPP.Classify.DataAccess
{
    public class DA_WMG002:DA_Matrix
    {
        public DA_WMG002()
        {
            tableName = "WMG.dbo.WMG001";
        }

        public DataTable GetSummaryData(string p_ym, string p_option)
        {
            if (string.Empty.Equals(p_option))
            {
                return GetSummaryData(p_ym);
            }
            string sql = string.Empty;
            switch (p_option)
            {
                case "存款類別":
                    sql = getSQLStatement存款類別();
                    break;
                case "客群別":
                    sql = getSQLStatement客群別();
                    break;
                case "群組":
                    sql = getSQLStatement群組();
                    break;
                case "存期別":
                    sql = getSQLStatement存期別();
                    break;
                case "定儲":
                    sql = getSQLStatement定儲();
                    break;
                case "定存":
                    sql = getSQLStatement定存();
                    break;  
                default:
                    break;
            }
            sql = string.Format(sql, getSUMStatement(),getCommonStatement());
            FillTableSchema("WMG001");
            FillFieldList(new string[] { p_ym }, new string[] { "@ym" }, "年月");
            AddParameter(sql);
            DataTable dt = getData();
            return dt;
        }

        public DataTable GetSummaryData(string p_ym)
        {
            FillTableSchema("WMG001");
            string sql = getSummaryStatement();
            FillFieldList(new string[] { p_ym }, new string[] { "@ym" },"年月" );
            AddParameter(sql);
            DataTable dt = getNewDataTable(getData());

            //在取得資料後，要在這自行運算出佔比
            string[] category = new[] { "定期存款", "零存整付", "整存整付"
                , "存本取息" };

            for (int x = 0; x < dt.Rows.Count; x++)
            {
                System.Data.DataRow row = dt.Rows[x];
                if (row["存款類別"].ToString().Equals(category[0]))
                {
                    cal(dt ,category, x);
                }
            }


            return dt;
            //return getData();
        }

        protected void cal(DataTable p_dt, string[] p_category, int p_i)
        {
            string tot_bal = "TOT_AVG_BAL";
            double total, result;
            total = 0.0;
            result = 0.0;
            
            foreach (string str in p_category)
            {
                //if (row["存款類別"].ToString().Equals(category[0]))
                if (p_dt.Rows[p_i]["存款類別"].ToString().Equals(str))
                {
                    total = Convert.ToDouble(p_dt.Rows[p_i][tot_bal].ToString());
                    p_i++;
                    result = Math.Round(Convert.ToDouble(
                        p_dt.Rows[p_i][tot_bal].ToString()) / total * 100, 2);
                    p_dt.Rows[p_i]["存款類別"] = string.Format("{0}%", result.ToString());
                    p_i++;
                    result = Math.Round(Convert.ToDouble(
                        p_dt.Rows[p_i][tot_bal].ToString()) / total * 100, 2);
                    p_dt.Rows[p_i]["存款類別"] = string.Format("{0}%", result.ToString());
                    p_i++;
                }                
            }
        }

        public override string[] GetDistinctField()
        {
            return new string[] { "年月" };
        }

        protected string getSummaryStatement()
        {
            return string.Format(@"
--↓
SELECT 
	[版塊別]
	,[客群別]
	,[群組]
	,CASE 
		WHEN A.存款類別 = '定期存款' AND A.利率型態 <> '' THEN ''
		WHEN A.存款類別 = '零存整付' AND A.利率型態 <> '' THEN ''
		WHEN A.存款類別 = '整存整付' AND A.利率型態 <> '' THEN ''
		WHEN A.存款類別 = '存本取息' AND A.利率型態 <> '' THEN ''
		WHEN A.存款類別 = '台幣定期' AND A.利率型態 = '機動' THEN ''
			ELSE A.存款類別 END AS [存款類別]
	,[利率型態]
	,CASE
		WHEN A.存款類別 = '台幣活期' THEN ''
		WHEN A.存款類別 = '台幣活存比' THEN ''	
		WHEN A.存款類別 = '台幣定期' THEN ''
			ELSE 
				CONVERT(VARCHAR, CAST(ISNULL(ROUND(TOT_BAL/1000000,0),0) AS MONEY ),1) 
		END AS TOT_BAL
	,CONVERT(VARCHAR, CAST(ISNULL(ROUND(TOT_AVG_BAL/1000000,0),0) AS MONEY),1)	AS TOT_AVG_BAL
	,SUBSTRING(CAST(ISNULL(AVG_RATE,0) * 100 AS VARCHAR),1,4) + '%' AS AVG_RATE
	,CASE
		WHEN A.存款類別 = '台幣活期' THEN ''
		WHEN A.存款類別 = '台幣活存比' THEN ''	
		WHEN A.存款類別 = '台幣定期' THEN ''
		ELSE 
			CONVERT(VARCHAR,CAST(ISNULL(ROUND(利息支出/1000000,2),0) AS MONEY),1) 
		END AS 利息支出
	,CASE
		WHEN A.存款類別 = '台幣活期' THEN ''
		WHEN A.存款類別 = '台幣活存比' THEN ''	
		WHEN A.存款類別 = '台幣定期' THEN ''
		ELSE 	
			SUBSTRING(CAST(ISNULL(FTP_RATE,0) * 100 AS VARCHAR),1,4) + '%' 
		END AS FTP_RATE
	,CASE
		WHEN A.存款類別 = '台幣活期' THEN ''
		WHEN A.存款類別 = '台幣活存比' THEN ''	
		WHEN A.存款類別 = '台幣定期' THEN ''
		ELSE 	
			CONVERT(VARCHAR,CAST(ISNULL(ROUND(FTP息/1000000,2),0) AS MONEY),1) 
		END AS FTP息
	,CASE
		WHEN A.存款類別 = '台幣活期' THEN ''
		WHEN A.存款類別 = '台幣活存比' THEN ''	
		WHEN A.存款類別 = '台幣定期' THEN ''
		ELSE 
			SUBSTRING(CAST((FTP_RATE - AVG_RATE)*100 AS VARCHAR),1,4)+ '%' 
		END AS 利差率
	,CASE
		WHEN A.存款類別 = '台幣活期' THEN ''
		WHEN A.存款類別 = '台幣活存比' THEN ''	
		WHEN A.存款類別 = '台幣定期' THEN ''
		ELSE 	
			CONVERT(VARCHAR,CAST(ISNULL(ROUND(利差/1000000,2),0) AS MONEY),1)
		END AS 利差金額
FROM(

	SELECT
		0 AS SEQ
		,'總存款' AS [版塊別]
		,'' AS [客群別]
		,'' AS [群組]
		,'' AS [存款類別]
		,'' AS [利率型態]
		{0}

	FROM WMG.dbo.WMG001 A
	WHERE A.年月 = @YM
	AND A.分行別 = '999'
	AND A.存款類別 IN ('零存整付','整存整付','定期存款','存本取息'
						,'支票存款','本行支票','活期存款','活期儲蓄'
						,'行員活期','外匯活期'	,'外匯定期')

	UNION ALL

	SELECT
		10 AS SEQ
		,'A1' AS [版塊別]
		,'' AS [客群別]
		,'' AS [群組]
		,'' AS [存款類別]
		,'' AS [利率型態]
		{0}
	FROM WMG.dbo.WMG001 A
	WHERE A.年月 = @YM
	AND A.分行別 = '999'
	AND A.存款類別 IN ('零存整付','整存整付','定期存款','存本取息'
						,'支票存款','本行支票','活期存款','活期儲蓄'
						,'行員活期','外匯活期'	,'外匯定期')
	AND A.客群別 = ' '
	AND A.版塊別 = 'A 1'


	UNION ALL

	SELECT
		20 AS SEQ
		,'A2' AS [版塊別]
		,'' AS [客群別]
		,'' AS [群組]
		,'' AS [存款類別]
		,'' AS [利率型態]
		{0}
	FROM WMG.dbo.WMG001 A
	WHERE A.年月 = @YM
	AND A.分行別 = '999'
	AND A.存款類別 IN ('零存整付','整存整付','定期存款','存本取息'
						,'支票存款','本行支票','活期存款','活期儲蓄'
						,'行員活期','外匯活期'	,'外匯定期')
	AND A.客群別 IN ('A','C','B','V')
    AND A.版塊別 = 'A 1'

	UNION ALL

    SELECT
		    25 AS SEQ
		    ,'A5' AS [版塊別]
		    ,'' AS [客群別]
		    ,'' AS [群組]
		    ,'' AS [存款類別]
		    ,'' AS [利率型態]
		   {0}
	    FROM WMG.dbo.WMG001 A
    	WHERE A.年月 = @YM
	    AND A.分行別 = '999'
	    AND A.存款類別 IN ('零存整付','整存整付','定期存款','存本取息'
						    ,'支票存款','本行支票','活期存款','活期儲蓄'
						    ,'行員活期','外匯活期'	,'外匯定期')
	    AND A.版塊別 = 'A 5'


    UNION ALL

	SELECT
		CASE 
			WHEN A.客群別 = 'V' THEN 30
			WHEN A.客群別 = 'A' THEN 40
			WHEN A.客群別 = 'B' THEN 50
			WHEN A.客群別 = 'C' THEN 60
			END SEQ
		,'' AS [版塊別]
		,CASE 
			WHEN A.客群別 = 'V' THEN 'P1'
			WHEN A.客群別 = 'A' THEN 'PA'
			WHEN A.客群別 = 'B' THEN 'PB'
			WHEN A.客群別 = 'C' THEN 'PC'
			END 客群別
		,'' AS [群組]
		,'' AS [存款類別]
		,'' AS [利率型態]
		{0}
	FROM WMG.dbo.WMG001 A
	WHERE A.年月 = @YM
	AND A.分行別 = '999'
	AND A.存款類別 IN ('零存整付','整存整付','定期存款','存本取息'
						,'支票存款','本行支票','活期存款','活期儲蓄'
						,'行員活期','外匯活期'	,'外匯定期')
	AND A.客群別 IN ('A','C','B','V')
	GROUP BY GROUPING SETS(A.客群別)

	UNION ALL
	--依群組小計
	SELECT
		CASE 
				WHEN A.群組 = 'V-Banking'	THEN 80
				WHEN A.群組 = '公教戶'	    THEN 90
				WHEN A.群組 = '行員'	    THEN 95
				WHEN A.群組 = '兒童戶'		THEN 100
				WHEN A.群組 = '愛心戶'		THEN 110
				WHEN A.群組 = '薪轉戶'		THEN 120
				WHEN A.群組 = '證券戶'		THEN 130
				WHEN A.群組 = '其他'		THEN 140
				ELSE 150 END SEQ
		,'' AS [版塊別]
		,'' AS [客群別]
		,CASE 
			WHEN A.群組 = ''	THEN '無註記'
			ELSE A.群組 END 群組		
		,'' AS [存款類別]
		,'' AS [利率型態]
		{0}
	FROM WMG.dbo.WMG001 A
	WHERE A.年月 = @YM
	AND A.分行別 = '999'
	--AND A.存款類別 NOT IN('銀行同業','郵匯局轉','公庫存款')
	AND A.存款類別 IN ('零存整付','整存整付','定期存款','存本取息'
						,'支票存款','本行支票','活期存款','活期儲蓄'
						,'行員活期','外匯活期'	,'外匯定期')

	GROUP BY GROUPING SETS(A.群組)

	UNION ALL

	SELECT
		CASE 
			WHEN A.存款類別 = '支票存款' THEN 160
			WHEN A.存款類別 = '本行支票' THEN 170
			WHEN A.存款類別 = '活期存款' THEN 180
			WHEN A.存款類別 = '活期儲蓄' THEN 190
			WHEN A.存款類別 = '行員活期' THEN 200
			WHEN A.存款類別 = '外匯活期' THEN 210
			WHEN A.存款類別 = '外匯定期' THEN 220
			WHEN A.存款類別 = '行員活期' THEN 230
			END SEQ
		,'' AS [版塊別]
		,'' AS [客群別]
		,'' AS [群組]
		,A.存款類別
		,'' AS [利率型態]
		
		{0}
	FROM WMG.dbo.WMG001 A
	WHERE A.年月 = @YM
	AND A.分行別 = '999'
	AND A.存款類別 IN 
		('支票存款','本行支票','活期存款','活期儲蓄','行員活期','外匯活期'
		,'外匯定期'	)
	GROUP BY GROUPING SETS(A.存款類別)

	UNION ALL

	SELECT
		CASE 
			WHEN A.存款類別 = '定期存款' AND A.利率型態 IS NULL  THEN 240
			WHEN A.存款類別 = '定期存款' AND A.利率型態 ='1' THEN 243
			WHEN A.存款類別 = '定期存款' AND A.利率型態 ='2' THEN 246
			WHEN A.存款類別 = '零存整付' AND A.利率型態 IS NULL  THEN 250
			WHEN A.存款類別 = '零存整付' AND A.利率型態 ='1' THEN 253
			WHEN A.存款類別 = '零存整付' AND A.利率型態 ='2' THEN 256
			WHEN A.存款類別 = '整存整付' AND A.利率型態 IS NULL  THEN 260
			WHEN A.存款類別 = '整存整付' AND A.利率型態 ='1' THEN 263
			WHEN A.存款類別 = '整存整付' AND A.利率型態 ='2' THEN 266		
			WHEN A.存款類別 = '存本取息' AND A.利率型態 IS NULL  THEN 270
			WHEN A.存款類別 = '存本取息' AND A.利率型態 ='1' THEN 273
			WHEN A.存款類別 = '存本取息' AND A.利率型態 ='2' THEN 276
			END SEQ
		,'' AS [版塊別]
		,'' AS [客群別]
		,'' AS [群組]
		,A.存款類別
		,CASE A.利率型態 
			WHEN '1' THEN '固定' 
			WHEN '2' THEN '機動'
			ELSE '' END AS 利率型態
		{0}
	FROM WMG.dbo.WMG001 A
	WHERE A.年月 = @YM
	AND A.分行別 = '999'
	AND A.存款類別 IN ('零存整付','整存整付','定期存款','存本取息')
	GROUP BY CUBE(A.存款類別,A.利率型態)


UNION ALL

SELECT 
	280 AS SEQ
	,'' AS [版塊別]
	,'' AS [客群別]
	,'' AS [群組]
	,'台幣活期' AS 存款類別
	,'' AS 利率型態
	,0 AS TOT_AVG_BAL
	,SUM(A.AVG_BAL) AS TOT_AVG_BAL	
	,0 AS AVG_RATE
	,'' AS 利息支出
	,0 AS [FTP_RATE]
	,0 AS FTP息
	,0 AS 利差
FROM WMG.dbo.WMG001 A
	WHERE A.年月 = @YM
	AND A.分行別 = '999'
	AND A.存款類別 IN 
		('支票存款','本行支票','活期存款','活期儲蓄','行員活期'	)

UNION ALL

SELECT 
	290 AS SEQ
	,'' AS [版塊別]
	,'' AS [客群別]
	,'' AS [群組]
	,'台幣定期' AS 存款類別
	,'' AS 利率型態
	,0 AS TOT_AVG_BAL
	,SUM(A.AVG_BAL) AS TOT_AVG_BAL	
	,0 AS AVG_RATE
	,'' AS 利息支出
	,0 AS [FTP_RATE]
	,0 AS FTP息
	,0 AS 利差
FROM WMG.dbo.WMG001 A
	WHERE A.年月 = @YM
	AND A.分行別 = '999'
	AND A.存款類別 IN ('零存整付','整存整付','定期存款','存本取息')		

UNION ALL

SELECT 
	300 AS SEQ
	,'' AS [版塊別]
	,'' AS [客群別]
	,'' AS [群組]
	,'台幣活存比' AS 存款類別
	,'' AS 利率型態
	,0 AS TOT_AVG_BAL
	,0 AS TOT_AVG_BAL 
	,SUM( 
		CASE A.存款類別
			WHEN '支票存款' THEN A.AVG_BAL
			WHEN '本行支票' THEN A.AVG_BAL
			WHEN '活期存款' THEN A.AVG_BAL
			WHEN '活期儲蓄' THEN A.AVG_BAL
			WHEN '行員活期'	 THEN A.AVG_BAL
			ELSE 0 END )/SUM(A.AVG_BAL) AS AVG_RATE
	,'' AS 利息支出
	,0 AS [FTP_RATE]
	,0 AS FTP息
	,0 AS 利差	
FROM WMG.dbo.WMG001 A
	WHERE A.年月 = @YM
	AND A.分行別 = '999'
	AND A.存款類別 IN 
		('支票存款','本行支票','活期存款','活期儲蓄','行員活期'	
		 ,'零存整付','整存整付','定期存款','存本取息'
		)

UNION ALL
SELECT 
310 AS SEQ
	,'' AS [版塊別]
	,'' AS [客群別]
	,'' AS [群組]
	,'台幣定期' AS 存款類別
	,'固定' AS 利率型態
	,0 AS TOT_AVG_BAL
	,SUM(
		CASE A.利率型態 		
			WHEN '1' THEN A.AVG_BAL
			ELSE 0 END 
		) AS TOT_AVG_BAL 
	,SUM(
		CASE A.利率型態 		
			WHEN '1' THEN A.AVG_BAL
			ELSE 0 END 
		) /SUM(A.AVG_BAL) AS AVG_RATE
	,'' AS 利息支出
	,0 AS [FTP_RATE]
	,0 AS FTP息
	,0 AS 利差	
FROM WMG.dbo.WMG001 A
	WHERE A.年月 = @YM
	AND A.分行別 = '999'
	AND A.存款類別 IN 
		('零存整付','整存整付','定期存款','存本取息'	)

UNION ALL

SELECT 
320 AS SEQ
	,'' AS [版塊別]
	,'' AS [客群別]
	,'' AS [群組]
	,'台幣定期' AS 存款類別
	,'機動' AS 利率型態
	,0 AS TOT_AVG_BAL
	,SUM(
		CASE A.利率型態 		
			WHEN '2' THEN A.AVG_BAL
			ELSE 0 END 
		) AS TOT_AVG_BAL 
	,SUM(
		CASE A.利率型態 		
			WHEN '2' THEN A.AVG_BAL
			ELSE 0 END 
		) /SUM(A.AVG_BAL) AS AVG_RATE
	,'' AS 利息支出
	,0 AS [FTP_RATE]
	,0 AS FTP息
	,0 AS 利差	
FROM WMG.dbo.WMG001 A
	WHERE A.年月 = @YM
	AND A.分行別 = '999'
	AND A.存款類別 IN 
		('零存整付','整存整付','定期存款','存本取息'	)


)A 
WHERE A.SEQ <= 400
ORDER BY A.SEQ", getSUMStatement());
        }

        protected DataTable getNewDataTable(DataTable p_dt)
        {
            DataTable newDT = new DataTable();
            foreach (DataColumn col in p_dt.Columns)
            {
                DataColumn newCol = new DataColumn();
                newCol.ColumnName = col.ColumnName;
                newCol.Caption = col.Caption;
                newCol.DataType = col.DataType;
                newDT.Columns.Add(newCol);
            }


            foreach (DataRow row in p_dt.Rows)
            {
                DataRow newRow = newDT.NewRow();
                for (int i = 0; i < p_dt.Columns.Count; i++)
                {
                    newRow[i] = row[i];
                }
                newDT.Rows.Add(newRow);
            }

            return newDT;
        }

        public override DataSet GetCriteria()
        {
            DataSet ds= base.GetCriteria();
            string sql = @"
SELECT CATEGORY FROM (
SELECT '存款類別'  AS CATEGORY, 1 SEQ
UNION
    SELECT '存期別',2
UNION
    SELECT '客群別',3
UNION
     SELECT '群組',4
UNION
     SELECT '定儲',5
UNION
     SELECT '定存',6

)A 
ORDER BY SEQ ";
            DataTable dt = getData(sql, "CATEGORY");
            ds.Tables.Add(dt);
            return ds;
        }

        protected string getSQLStatement存期別()
        {
            return @"
SELECT
	類別
	,TXT [存期]
    {1}
FROM(
SELECT 
	'定儲' [類別]
	,SUBSTRING(B.TXT,3,20) AS TXT
    {0}
FROM 
(
	SELECT 12 [L_BOUND],23 [R_BOUND],'1_12~23個月' [TXT]
UNION
	SELECT 24,35,'2_24~35個月'
UNION	
	SELECT 36,1000,'3_36個月以上'

)B LEFT JOIN (

				SELECT
						CASE 
							WHEN A.存款類別 = '零存整付'  THEN 250
							WHEN A.存款類別 = '整存整付'  THEN 260
							WHEN A.存款類別 = '存本取息'  THEN 270
							END SEQ
						,A.存款類別
						,A.BAL
						,A.AVG_BAL
						,a.AVG_RATE
						,a.幣別
						,a.年月
						,a.FTP_RATE
						,A.ORG_TERM
					FROM WMG.dbo.WMG001 A
					WHERE A.年月 = @YM
					AND A.分行別 < '999'
					--AND A.存款類別 IN ('定期存款') 
					AND A.存款類別 IN ('零存整付','整存整付','存本取息')
				)A
ON A.ORG_TERM >= B.L_BOUND AND A.ORG_TERM <= B.R_BOUND				
GROUP BY GROUPING SETS(B.TXT)

UNION ALL

SELECT 
	'定期' 
	,SUBSTRING(B.TXT,3,20) AS TXT
    {0}
FROM 
(
	SELECT 1 [L_BOUND],2 [R_BOUND],'1_1~2個月' [TXT]
UNION
	SELECT 3,5,'2_3~5個月'
UNION	
	SELECT 6,8,'3_6~8個月'
UNION	
	SELECT 9,11,'4_9~11個月'
UNION
	SELECT 12,23,'5_12~23個月'
UNION	
	SELECT 24,35,'6_24~35個月'
UNION
	SELECT 36,1000,'7_36個月以上'
)B LEFT JOIN (

				SELECT
						A.存款類別
						,A.BAL
						,A.AVG_BAL
						,a.AVG_RATE
						,a.幣別
						,a.年月
						,a.FTP_RATE
						,A.ORG_TERM
					FROM WMG.dbo.WMG001 A
    				WHERE A.年月 = @YM
					AND A.分行別 < '999'
					AND A.存款類別 IN ('定期存款') 
				)A
ON A.ORG_TERM >= B.L_BOUND AND A.ORG_TERM <= B.R_BOUND				
GROUP BY GROUPING SETS(B.TXT)
)A
";
        
        }

        protected string getSQLStatement定儲()
        {
            return @"
SELECT 
	SUBSTRING(B.TXT,3,20) AS TXT
    {0}
FROM 
(
	SELECT 12 [L_BOUND],23 [R_BOUND],'1_12~23個月' [TXT]
UNION
	SELECT 24,35,'2_24~35個月'
UNION	
	SELECT 36,1000,'3_36個月以上'

)B LEFT JOIN (

				SELECT
						CASE 
							WHEN A.存款類別 = '零存整付'  THEN 250
							WHEN A.存款類別 = '整存整付'  THEN 260
							WHEN A.存款類別 = '存本取息'  THEN 270
							END SEQ
						,A.存款類別
						,A.BAL
						,A.AVG_BAL
						,a.AVG_RATE
						,a.幣別
						,a.年月
						,a.FTP_RATE
						,A.ORG_TERM
					FROM WMG.dbo.WMG001 A
					WHERE A.年月 = @YM
					AND A.分行別 < '999'
					--AND A.存款類別 IN ('定期存款') 
					AND A.存款類別 IN ('零存整付','整存整付','存本取息')
				)A
ON A.ORG_TERM >= B.L_BOUND AND A.ORG_TERM <= B.R_BOUND				
GROUP BY CUBE(B.TXT)
ORDER BY B.TXT
";
        }

        protected string getSQLStatement定存()
        {
            return @"
SELECT 
	SUBSTRING(B.TXT,3,20) AS TXT
    {0}
FROM 
(
	SELECT 1 [L_BOUND],2 [R_BOUND],'1_1~2個月' [TXT]
UNION
	SELECT 3,5,'2_3~5個月'
UNION	
	SELECT 6,8,'3_6~8個月'
UNION	
	SELECT 9,11,'4_9~11個月'
UNION
	SELECT 12,23,'5_12~23個月'
UNION	
	SELECT 24,35,'6_24~35個月'
UNION
	SELECT 36,1000,'7_36個月以上'
)B LEFT JOIN (

				SELECT
						A.存款類別
						,A.BAL
						,A.AVG_BAL
						,a.AVG_RATE
						,a.幣別
						,a.年月
						,a.FTP_RATE
						,A.ORG_TERM
					FROM WMG.dbo.WMG001 A
					WHERE A.年月 = @YM
					AND A.分行別 < '999'
					AND A.存款類別 IN ('定期存款') 
				)A
ON A.ORG_TERM >= B.L_BOUND AND A.ORG_TERM <= B.R_BOUND				
GROUP BY CUBE(B.TXT)
ORDER BY B.TXT"

                ;
        }

        protected string getSQLStatement存款類別()
        {
            return @"
SELECT 
    存款類別
    {1}
FROM (
SELECT
		CASE 
			WHEN A.存款類別 = '支票存款' THEN 160
			WHEN A.存款類別 = '本行支票' THEN 170
			WHEN A.存款類別 = '活期存款' THEN 180
			WHEN A.存款類別 = '活期儲蓄' THEN 190
			WHEN A.存款類別 = '行員活期' THEN 200
			WHEN A.存款類別 = '外匯活期' THEN 210
			WHEN A.存款類別 = '外匯定期' THEN 220
			WHEN A.存款類別 = '行員活期' THEN 230
			END SEQ
		,A.存款類別
        {0}		
	FROM WMG.dbo.WMG001 A
	WHERE A.年月 = @YM
	AND A.分行別 = '999'
	AND A.存款類別 IN 
		('支票存款','本行支票','活期存款','活期儲蓄','行員活期','外匯活期'
		,'外匯定期'	)
	GROUP BY GROUPING SETS(A.存款類別)
)A ORDER BY SEQ";
        }

        protected string getSQLStatement群組()
        {
            return @"
SELECT 
    群組
    {1}
FROM (
SELECT
		CASE 
				WHEN A.群組 = 'V-Banking'	THEN 80
				WHEN A.群組 = '公教戶'	    THEN 90
				WHEN A.群組 = '行員'	    THEN 95
				WHEN A.群組 = '兒童戶'		THEN 100
				WHEN A.群組 = '愛心戶'		THEN 110
				WHEN A.群組 = '薪轉戶'		THEN 120
				WHEN A.群組 = '證券戶'		THEN 130
				WHEN A.群組 = '其他'		THEN 140
				ELSE 150 END SEQ
		,CASE 
			WHEN A.群組 = ''	THEN '無註記'
			ELSE A.群組 END 群組		
		{0}
	FROM WMG.dbo.WMG001 A
	WHERE A.年月 = @YM
	AND A.分行別 = '999'
	AND A.存款類別 IN ('零存整付','整存整付','定期存款','存本取息'
						,'支票存款','本行支票','活期存款','活期儲蓄'
						,'行員活期','外匯活期'	,'外匯定期')

	GROUP BY GROUPING SETS(A.群組)
)A ORDER BY SEQ";
        }

        protected string getSQLStatement客群別()
        {
            return @"
SELECT	
    客群別
    {1}
FROM (
SELECT
		CASE 
			WHEN A.客群別 = 'V' THEN 30
			WHEN A.客群別 = 'A' THEN 40
			WHEN A.客群別 = 'B' THEN 50
			WHEN A.客群別 = 'C' THEN 60
			END SEQ
		,CASE 
			WHEN A.客群別 = 'V' THEN 'P1'
			WHEN A.客群別 = 'A' THEN 'PA'
			WHEN A.客群別 = 'B' THEN 'PB'
			WHEN A.客群別 = 'C' THEN 'PC'
			END 客群別
            {0}
	FROM WMG.dbo.WMG001 A
	WHERE A.年月 = @YM
	AND A.分行別 = '999'
	AND A.存款類別 IN ('零存整付','整存整付','定期存款','存本取息'
						,'支票存款','本行支票','活期存款','活期儲蓄'
						,'行員活期','外匯活期'	,'外匯定期')
	AND A.客群別 IN ('A','C','B','V')
	GROUP BY GROUPING SETS(A.客群別)
) A ORDER BY SEQ
";
        }

        protected string getSUMStatement()
        {
            return @"
		,SUM(A.BAL) AS TOT_BAL
		,SUM(A.AVG_BAL) AS TOT_AVG_BAL	
		,SUM(dbo.GetInterestExpense(a.AVG_BAL,a.AVG_RATE,a.幣別,a.存款類別,a.年月))
			*12/SUM(A.AVG_BAL) AS AVG_RATE
		,SUM(dbo.GetInterestExpense(a.AVG_BAL,a.AVG_RATE,a.幣別,a.存款類別,a.年月)) AS 利息支出
		,ROUND(SUM(dbo.GetFtpRate(a.幣別,a.存款類別,a.AVG_BAL,a.FTP_RATE,a.年月))
			/31*365/SUM(A.AVG_BAL),4) AS [FTP_RATE]
		,SUM(dbo.GetFtpRate(a.幣別,a.存款類別,a.AVG_BAL,a.FTP_RATE,a.年月))AS FTP息
		,SUM(dbo.GetFtpRate(a.幣別,a.存款類別,a.AVG_BAL,a.FTP_RATE,a.年月)) 
		 - SUM(dbo.GetInterestExpense(a.AVG_BAL,a.AVG_RATE,a.幣別,a.存款類別,a.年月)) AS 利差
";
        }

        protected string getCommonStatement()
        {
            return @"
	,CONVERT(NVARCHAR,CAST(TOT_BAL AS MONEY),1) TOT_BAL
	,CONVERT(NVARCHAR,CAST(TOT_AVG_BAL AS MONEY),1) TOT_AVG_BAL
	,AVG_RATE
	,CONVERT(NVARCHAR,CAST(利息支出 AS MONEY),1) 利息支出	
	,[FTP_RATE]
	,CONVERT(NVARCHAR,CAST(FTP息 AS MONEY),1) FTP息
	,CONVERT(NVARCHAR,CAST(利差 AS MONEY),1) 利差
";
        }
    }

    
}
